Imports System.Data.SqlClient
Imports System.Text

Friend Class DBWaitStateLog_Factory
    Inherits DBBase

    Public Function GetDescriptionForState(ByVal sWaitState As String) As String
        Dim sSQL As New StringBuilder("select")
        sSQL.Append(" [desc]")
        sSQL.Append(" from")
        sSQL.Append(" WaitStates")
        sSQL.Append(" where")
        sSQL.Append(" [name]=" & ValueToDBValue(sWaitState))

        Return MyBase.ExecuteScalar(sSQL.ToString)
    End Function

    Public Function WaitState10PointTrends( _
                ByVal lDatabaseID As Long, _
                ByVal sStartDate As String, _
                ByVal sEndDate As String, _
                Optional ByVal sWaitState As String = "") As DataTable

        Dim oData As New DataTable

        Dim sSQL As String = "p_WaitState10PointTrends"

        Dim oCon As New SqlConnection(msConnectionString)
        oCon.Open()

        Dim oCMD As New SqlCommand(sSQL, oCon)
        oCMD.CommandType = CommandType.StoredProcedure

        Dim oParm As SqlParameter

        oParm = New SqlParameter("@DatabaseID", SqlDbType.BigInt)
        oParm.Value = lDatabaseID
        oCMD.Parameters.Add(oParm)

        oParm = New SqlParameter("@StartDate", SqlDbType.DateTime)
        oParm.Value = sStartDate
        oCMD.Parameters.Add(oParm)

        oParm = New SqlParameter("@EndDate", SqlDbType.DateTime)
        oParm.Value = sEndDate
        oCMD.Parameters.Add(oParm)

        If sWaitState > "" Then
            oParm = New SqlParameter("@WaitState", SqlDbType.VarChar, 50)
            oParm.Value = sWaitState
            oCMD.Parameters.Add(oParm)
        End If

        Dim oDT As New SqlDataAdapter(oCMD)

        oDT.Fill(oData)

        oDT.Dispose()
        oCMD.Dispose()
        oCon.Close()
        oCon.Dispose()

        oDT.Dispose()

        Return oData
    End Function

    Public Function WaitStateDetail( _
                        ByVal lDatabaseID As Long, _
                        ByVal sStartDate As String, _
                        ByVal sEndDate As String, _
                        ByVal sWaitState As String) As DataTable

        Dim oData As New DataTable

        Dim sSQL As String = "p_AverageDayWaitStates"

        Dim oCon As New SqlConnection(msConnectionString)
        oCon.Open()

        Dim oCMD As New SqlCommand(sSQL, oCon)
        oCMD.CommandType = CommandType.StoredProcedure

        Dim oParm As SqlParameter

        oParm = New SqlParameter("@BeginDate", SqlDbType.DateTime)
        oParm.Value = sStartDate
        oCMD.Parameters.Add(oParm)

        oParm = New SqlParameter("@EndDate", SqlDbType.DateTime)
        oParm.Value = sEndDate
        oCMD.Parameters.Add(oParm)

        oParm = New SqlParameter("@WaitType", SqlDbType.VarChar, 50)
        oParm.Value = sWaitState
        oCMD.Parameters.Add(oParm)

        oParm = New SqlParameter("@DatabaseID", SqlDbType.BigInt)
        oParm.Value = lDatabaseID
        oCMD.Parameters.Add(oParm)

        Dim oDT As New SqlDataAdapter(oCMD)

        oDT.Fill(oData)

        oDT.Dispose()
        oCMD.Dispose()
        oCon.Close()
        oCon.Dispose()

        oDT.Dispose()

        Return oData
    End Function

    Public Sub RemoveExpiredData(ByVal iExpirationPeriod As Integer)
        Dim sSQL As New StringBuilder("delete from dbo.WaitStateHistory")
        sSQL.Append(" where")
        sSQL.Append("   datediff(day,[CreateDate],current_timestamp)>" & iExpirationPeriod)

        MyBase.ExecuteNonQuery(sSQL.ToString)
    End Sub
End Class
